{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "0e1682fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Py3_Jupyter_Nb_讯捷集团_回填毛利(随机森林回归)_GF_2024-01-04.ipynb\n",
    "# Create By GF 2024-01-04 17:33"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7eda483c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "# --------------------------------------------------\n",
    "import pyspark\n",
    "from pyspark.sql import Row, SparkSession\n",
    "from pyspark.sql.functions import asc, avg, col\n",
    "from pyspark.sql.types import DateType, IntegerType, DoubleType\n",
    "from pyspark.sql.window import Window\n",
    "# --------------------------------------------------\n",
    "from pyspark.ml.feature import StringIndexer, VectorAssembler\n",
    "from pyspark.ml.regression import RandomForestRegressor\n",
    "from pyspark.ml.evaluation import RegressionEvaluator"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "2aa50353",
   "metadata": {},
   "outputs": [],
   "source": [
    "def MapFunc_SparkSQL_Row_Add(SrcRow:pyspark.sql.types.Row, FldName:str, FldVal:object) -> pyspark.sql.types.Row: \n",
    "\n",
    "    \"\"\"\n",
    "    [Require] import pyspark\n",
    "    \n",
    "    [Example] SrcRow = Row(日期=datetime.date(2023, 12, 1), 门店名称='贵州毕节黔西形象店', 毛利='5463.482')\n",
    "\n",
    "              NewRow = MapFunc_SparkSQL_Row_Add(SrcRow=SrcRow, FldName=\"星期\", FldVal=SrcRow[\"日期\"].weekday())\n",
    "\n",
    "              print(NewRow)\n",
    "              >>> Row(日期=datetime.date(2023, 12, 1), 门店名称='贵州毕节黔西形象店', 毛利='5463.482', 星期=4)\n",
    "    \"\"\"\n",
    "    \n",
    "    # Convert Obj Row to Dict. \n",
    "    # ----------------------------------------------\n",
    "    Row_Dict = SrcRow.asDict()\n",
    "    \n",
    "    # Add a New Key in the Dictionary With the New Column Name and Value.\n",
    "    # ----------------------------------------------\n",
    "    Row_Dict[FldName] = FldVal\n",
    "    \n",
    "    # Convert Dict to Obj Row. \n",
    "    # ----------------------------------------------\n",
    "    NewRow = Row(**Row_Dict)\n",
    "    \n",
    "    # ##############################################\n",
    "    return NewRow\n",
    "\n",
    "def MapFunc_XunJie_Store_Level_Return_Index(StrStoreLevel:str) -> int:\n",
    "    \n",
    "    if   (StrStoreLevel == \"A+1\"):\n",
    "        return int(11)\n",
    "    elif (StrStoreLevel == \"A+2\"):\n",
    "        return int(10)\n",
    "    elif (StrStoreLevel == \"A+3\"):\n",
    "        return int(9)\n",
    "    elif (StrStoreLevel == \"A1\"):\n",
    "        return int(8)\n",
    "    elif (StrStoreLevel == \"A1预\"):\n",
    "        return int(7)\n",
    "    elif (StrStoreLevel == \"A2\"):\n",
    "        return int(6)\n",
    "    elif (StrStoreLevel == \"B1\"):\n",
    "        return int(5)\n",
    "    elif (StrStoreLevel == \"B1预\"):\n",
    "        return int(4)\n",
    "    elif (StrStoreLevel == \"B2\"):\n",
    "        return int(3)\n",
    "    elif (StrStoreLevel == \"C\"):\n",
    "        return int(2)\n",
    "    elif (StrStoreLevel == \"D\"):\n",
    "        return int(1)\n",
    "    else:\n",
    "        return int(0)\n",
    "\n",
    "def DtmFunc_Weekday_Return_String_CN(SrcDtm:datetime.datetime) -> str:\n",
    "    \n",
    "    \"\"\"[Require] import datetime\"\"\"\n",
    "    \n",
    "    Weekday_Str_Chinese:list = [\"周一\", \"周二\", \"周三\", \"周四\", \"周五\", \"周六\", \"周日\"]\n",
    "    \n",
    "    # ##############################################\n",
    "    return Weekday_Str_Chinese[SrcDtm.weekday()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d773e855",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Spark 2.0 以上版本的 spark-shell 在启动时会自动创建一个名为 spark 的 SparkSession 对象。\n",
    "# 当需要手工创建时，SparkSession 可以由其伴生对象的 builder 方法创建出来。\n",
    "# --------------------------------------------------\n",
    "spark = SparkSession.builder.master(\"local[*]\").appName(\"MachineLearn\").config(\"spark.driver.memory\", \"8g\").getOrCreate()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "141f164f",
   "metadata": {},
   "outputs": [],
   "source": [
    "StoreProfitSDF = spark.read.option(\"header\",\"true\") \\\n",
    "                           .option(\"encoding\", \"gbk\") \\\n",
    "                           .csv(\"file:///C:/Jupyter/Datas/CSV数据_讯捷集团_毛利数据_门店毛利数据_2020-01-01至2023-12-31_GB2312.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b6d36e57",
   "metadata": {},
   "outputs": [],
   "source": [
    "StoreProfitSDF = StoreProfitSDF.withColumn(\"日期\", col(\"日期\").cast(DateType()))\n",
    "StoreProfitSDF = StoreProfitSDF.withColumn(\"年\",   col(\"年\").cast(IntegerType()))\n",
    "StoreProfitSDF = StoreProfitSDF.withColumn(\"月\",   col(\"月\").cast(IntegerType()))\n",
    "StoreProfitSDF = StoreProfitSDF.withColumn(\"毛利\", col(\"毛利\").cast(DoubleType()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2fa41fdc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Only No Null Data Set Rows Number: 266844\n",
      "Only No Null Data Set Rows Number: 266840 (Clear Empty Values Again)\n",
      "Only Is Null Data Set Rows Number: 76594\n",
      "+----------+----+---+--------+--------+------------------------+--------+--------+-------------+---------+\n",
      "|      日期|  年| 月|企业部门|企业体系|                门店名称|门店等级|区域类别|     区域商圈|     毛利|\n",
      "+----------+----+---+--------+--------+------------------------+--------+--------+-------------+---------+\n",
      "|2023-12-01|2023| 12|    贵州|    形象|      贵州毕节黔西形象店|       C|    外围|街铺-复合商圈| 5463.482|\n",
      "|2023-12-01|2023| 12|    贵州|    华为|          贵州开阳华为店|       C|    二级|街铺-复合商圈|   797.06|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|    贵州遵义丁字口形象店|     A+1|    二级|街铺-复合商圈| 23408.76|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|      贵州贵阳黄河形象店|      A1|    郊县|街铺-复合商圈|  7217.09|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|    贵州凯里新世纪形象店|      A2|    二级|街铺-通讯商圈|25397.361|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|          贵州安顺形象店|      A1|    二级|街铺-复合商圈|19133.544|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|      贵州贵阳白云形象店|     A+1|    郊县|街铺-复合商圈|  6410.44|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|          贵州开阳形象店|      B1|    二级|街铺-复合商圈| 3567.117|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|    贵州遵义珠海路形象店|      A1|    二级|街铺-复合商圈| 7818.307|\n",
      "|2023-12-01|2023| 12|    贵州|    华为|    贵州遵义丁字口华为店|       C|    二级|街铺-复合商圈| 10013.29|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|    贵州贵阳鸿通城形象店|      A1|    外围| 综合体外商圈| 6211.289|\n",
      "|2023-12-01|2023| 12|    贵州|    华为|贵州贵阳白云同心路华为店|       C|    郊县|街铺-复合商圈| 5289.009|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|      贵州毕节织金形象店|      B1|    二级|街铺-复合商圈|  3346.11|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|      贵州贵阳金阳形象店|      A2|    郊县| 综合体外商圈|27390.398|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|  贵州清镇三角花园形象店|      A1|    郊县|街铺-复合商圈|18729.508|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|贵州遵义中建方圆荟形象店|       D|    外围| 综合体内商圈|    379.0|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|贵州六盘水钟山大道形象店|      A1|    二级|街铺-复合商圈|  6675.55|\n",
      "|2023-12-01|2023| 12|    贵州|    华为|      贵州贵阳虹祥华为店|      B1|    市区|街铺-复合商圈|  9307.56|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|          贵州兴义形象店|      B2|    二级|街铺-复合商圈|  9592.87|\n",
      "|2023-12-01|2023| 12|    贵州|    形象|贵州六盘水人民广场形象店|      A2|    二级|街铺-复合商圈|  9026.04|\n",
      "+----------+----+---+--------+--------+------------------------+--------+--------+-------------+---------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 筛选数据列。\n",
    "# --------------------------------------------------\n",
    "FilteredColSDF = StoreProfitSDF.select([\"日期\", \"年\", \"月\", \"企业部门\", \"企业体系\", \"门店名称\", \"门店等级\", \"区域类别\", \"区域商圈\", \"毛利\"])\n",
    "\n",
    "# 筛选数据集。\n",
    "# --------------------------------------------------\n",
    "NoNullSDF = FilteredColSDF.filter(col(\"毛利\").isNull() == False)\n",
    "IsNullSDF = FilteredColSDF.filter(col(\"毛利\").isNull() == True)\n",
    "\n",
    "print(\"Only No Null Data Set Rows Number: %d\" % NoNullSDF.count())\n",
    "\n",
    "# 清除空值行。\n",
    "# --------------------------------------------------\n",
    "# SparkDataFrame.na.drop(how =\"any/all\", thresh=threshold_value，subset =[\"col_name_1\", \"col_name_2\"])\n",
    "# -> how: 这需要两个值中的任何一个 any 或 all。(any: 如果任何列包含空值, 则删除一行 / all: 如果所有列都包含空值，则删除一行 / 默认值为 any)\n",
    "# -> thresh: 这将取整数值, 并删除小于 thresh 且保存非空值的行。(默认情况下, 它设置为 None)\n",
    "# -> subset: 该参数用于选择特定的列, 以其中的空值为目标。(默认情况下, 它是 None)\n",
    "NoNullSDF = NoNullSDF.na.drop(how=\"any\", subset =[\"门店等级\", \"区域类别\", \"区域商圈\"])\n",
    "\n",
    "print(\"Only No Null Data Set Rows Number: %d (Clear Empty Values Again)\" % NoNullSDF.count())\n",
    "print(\"Only Is Null Data Set Rows Number: %d\" % IsNullSDF.count())\n",
    "\n",
    "NoNullSDF.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "a8836f18",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----+---+--------+--------+--------------------+--------+--------+-------------+---------+-----------+\n",
      "|      日期|  年| 月|企业部门|企业体系|            门店名称|门店等级|区域类别|     区域商圈|     毛利|毛利(AVG60)|\n",
      "+----------+----+---+--------+--------+--------------------+--------+--------+-------------+---------+-----------+\n",
      "|2020-01-01|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  6106.81|    6106.81|\n",
      "|2020-01-02|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  1603.74|   3855.275|\n",
      "|2020-01-03|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  1246.31|    2985.62|\n",
      "|2020-01-04|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  2989.19|  2986.5125|\n",
      "|2020-01-05|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|   2247.9|    2838.79|\n",
      "|2020-01-06|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  1601.07|  2632.5033|\n",
      "|2020-01-07|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|   227.57|  2288.9414|\n",
      "|2020-01-08|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|   883.26|  2113.2313|\n",
      "|2020-01-09|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|1683.0299|  2065.4311|\n",
      "|2020-01-10|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|4387.8599|   2297.674|\n",
      "|2020-01-11|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  2529.09|  2318.7118|\n",
      "|2020-01-12|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|5315.4498|    2568.44|\n",
      "|2020-01-13|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  2406.25|  2555.9638|\n",
      "|2020-01-14|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  1243.11|  2462.1885|\n",
      "|2020-01-15|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|3582.2399|  2536.8586|\n",
      "|2020-01-16|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|3222.3699|  2579.7031|\n",
      "|2020-01-17|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|5797.7597|  2769.0005|\n",
      "|2020-01-18|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  4574.45|  2869.3033|\n",
      "|2020-01-19|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  6704.83|  3071.1731|\n",
      "|2020-01-20|2020|  1|    二部|    华为|四川温江文庙街华为店|       C|    郊县|街铺-通讯商圈|  4033.71|     3119.3|\n",
      "+----------+----+---+--------+--------+--------------------+--------+--------+-------------+---------+-----------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "CalcSDF = NoNullSDF\n",
    "CalcSDF = CalcSDF.withColumn(\"毛利(AVG60)\", avg(\"毛利\").over(Window.partitionBy(\"门店名称\").orderBy(asc(\"日期\")).rowsBetween(-60, Window.currentRow)))\n",
    "CalcSDF = CalcSDF.withColumn(\"毛利(AVG60)\", pyspark.sql.functions.round(col(\"毛利(AVG60)\"), 4))\n",
    "\n",
    "CalcSDF.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "afdb46bc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Row(日期=datetime.date(2020, 1, 1), 年=2020, 月=1, 企业部门='二部', 企业体系='华为', 门店名称='四川温江文庙街华为店', 门店等级='C', 区域类别='郊县', 区域商圈='街铺-通讯商圈', 毛利=6106.81, 毛利(AVG60)=6106.81, 星期(Idx)=2, 星期='周三', 门店等级(Idx)=2),\n",
       " Row(日期=datetime.date(2020, 1, 2), 年=2020, 月=1, 企业部门='二部', 企业体系='华为', 门店名称='四川温江文庙街华为店', 门店等级='C', 区域类别='郊县', 区域商圈='街铺-通讯商圈', 毛利=1603.74, 毛利(AVG60)=3855.275, 星期(Idx)=3, 星期='周四', 门店等级(Idx)=2),\n",
       " Row(日期=datetime.date(2020, 1, 3), 年=2020, 月=1, 企业部门='二部', 企业体系='华为', 门店名称='四川温江文庙街华为店', 门店等级='C', 区域类别='郊县', 区域商圈='街铺-通讯商圈', 毛利=1246.31, 毛利(AVG60)=2985.62, 星期(Idx)=4, 星期='周五', 门店等级(Idx)=2),\n",
       " Row(日期=datetime.date(2020, 1, 4), 年=2020, 月=1, 企业部门='二部', 企业体系='华为', 门店名称='四川温江文庙街华为店', 门店等级='C', 区域类别='郊县', 区域商圈='街铺-通讯商圈', 毛利=2989.19, 毛利(AVG60)=2986.5125, 星期(Idx)=5, 星期='周六', 门店等级(Idx)=2),\n",
       " Row(日期=datetime.date(2020, 1, 5), 年=2020, 月=1, 企业部门='二部', 企业体系='华为', 门店名称='四川温江文庙街华为店', 门店等级='C', 区域类别='郊县', 区域商圈='街铺-通讯商圈', 毛利=2247.9, 毛利(AVG60)=2838.79, 星期(Idx)=6, 星期='周日', 门店等级(Idx)=2)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CalcRDD = CalcSDF.rdd\n",
    "CalcRDD = CalcRDD.map(lambda X: MapFunc_SparkSQL_Row_Add(X, \"星期(Idx)\", X[\"日期\"].weekday()))\n",
    "CalcRDD = CalcRDD.map(lambda X: MapFunc_SparkSQL_Row_Add(X, \"星期\", DtmFunc_Weekday_Return_String_CN(X[\"日期\"])))\n",
    "CalcRDD = CalcRDD.map(lambda X: MapFunc_SparkSQL_Row_Add(X, \"门店等级(Idx)\", MapFunc_XunJie_Store_Level_Return_Index(X[\"门店等级\"])))\n",
    "\n",
    "CalcRDD.take(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "fcd6c743",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Calculated and Cached 266840 Rows of Data(RDD/DF)\n"
     ]
    }
   ],
   "source": [
    "# 缓存数据(Cache RDD/DF)。\n",
    "# --------------------------------------------------\n",
    "CalcSDF = CalcRDD.toDF()\n",
    "# ..................................................\n",
    "CalcSDF.cache() # -> 缓存 RDD/DF。\n",
    "CalcSDF.persist()\n",
    "RowsNumber = CalcSDF.count() # -> 使用 Action 触发缓存操作。\n",
    "print(\"Calculated and Cached %d Rows of Data(RDD/DF)\" % RowsNumber)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f921904b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+--------+-------------+--------+-------------+--------------------+--------+-------------+-----------+\n",
      "|      日期|企业部门|企业部门(Idx)|企业体系|企业体系(Idx)|            门店名称|门店等级|门店等级(Idx)|毛利(AVG60)|\n",
      "+----------+--------+-------------+--------+-------------+--------------------+--------+-------------+-----------+\n",
      "|2020-01-01|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|    6106.81|\n",
      "|2020-01-02|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|   3855.275|\n",
      "|2020-01-03|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|    2985.62|\n",
      "|2020-01-04|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|  2986.5125|\n",
      "|2020-01-05|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|    2838.79|\n",
      "|2020-01-06|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|  2632.5033|\n",
      "|2020-01-07|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|  2288.9414|\n",
      "|2020-01-08|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|  2113.2313|\n",
      "|2020-01-09|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|  2065.4311|\n",
      "|2020-01-10|    二部|          5.0|    华为|          1.0|四川温江文庙街华为店|       C|            2|   2297.674|\n",
      "+----------+--------+-------------+--------+-------------+--------------------+--------+-------------+-----------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "SrcColName:list = [\"企业部门\",      \"企业体系\",      \"区域类别\",      \"区域商圈\"]\n",
    "NewColName:list = [\"企业部门(Idx)\", \"企业体系(Idx)\", \"区域类别(Idx)\", \"区域商圈(Idx)\"]\n",
    "FeaColName:list = [\"年\", \"月\", \"星期(Idx)\", \"企业部门(Idx)\", \"企业体系(Idx)\", \"门店等级(Idx)\", \"区域类别(Idx)\", \"区域商圈(Idx)\"]\n",
    "\n",
    "# 使用 StringIndexer 转换[星期]列, 并使用 .fit() 拟合数据, 最后使用 .transform() 转换数据.\n",
    "# --------------------------------------------------\n",
    "IndexedSDF = StringIndexer(inputCols=SrcColName, outputCols=NewColName).fit(CalcSDF).transform(CalcSDF)\n",
    "\n",
    "IndexedSDF.select([\"日期\", \"企业部门\", \"企业部门(Idx)\", \"企业体系\", \"企业体系(Idx)\", \"门店名称\",\n",
    "                   \"门店等级\", \"门店等级(Idx)\", \"毛利(AVG60)\"]).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "0e1eb16f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----+--------+--------+--------------------+--------+-----------+--------------------+\n",
      "|      日期|星期|企业部门|企业体系|            门店名称|门店等级|毛利(AVG60)|            Features|\n",
      "+----------+----+--------+--------+--------------------+--------+-----------+--------------------+\n",
      "|2020-01-01|周三|    二部|    华为|四川温江文庙街华为店|       C|    6106.81|[2020.0,1.0,2.0,5...|\n",
      "|2020-01-02|周四|    二部|    华为|四川温江文庙街华为店|       C|   3855.275|[2020.0,1.0,3.0,5...|\n",
      "|2020-01-03|周五|    二部|    华为|四川温江文庙街华为店|       C|    2985.62|[2020.0,1.0,4.0,5...|\n",
      "|2020-01-04|周六|    二部|    华为|四川温江文庙街华为店|       C|  2986.5125|[2020.0,1.0,5.0,5...|\n",
      "|2020-01-05|周日|    二部|    华为|四川温江文庙街华为店|       C|    2838.79|[2020.0,1.0,6.0,5...|\n",
      "|2020-01-06|周一|    二部|    华为|四川温江文庙街华为店|       C|  2632.5033|[2020.0,1.0,0.0,5...|\n",
      "|2020-01-07|周二|    二部|    华为|四川温江文庙街华为店|       C|  2288.9414|[2020.0,1.0,1.0,5...|\n",
      "|2020-01-08|周三|    二部|    华为|四川温江文庙街华为店|       C|  2113.2313|[2020.0,1.0,2.0,5...|\n",
      "|2020-01-09|周四|    二部|    华为|四川温江文庙街华为店|       C|  2065.4311|[2020.0,1.0,3.0,5...|\n",
      "|2020-01-10|周五|    二部|    华为|四川温江文庙街华为店|       C|   2297.674|[2020.0,1.0,4.0,5...|\n",
      "+----------+----+--------+--------+--------------------+--------+-----------+--------------------+\n",
      "only showing top 10 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 使用 VectorAssembler 将特征列合并成一个特征向量。\n",
    "# --------------------------------------------------\n",
    "AssembledSDF = VectorAssembler(inputCols=FeaColName, outputCol=\"Features\").transform(IndexedSDF)\n",
    "\n",
    "AssembledSDF.select([\"日期\", \"星期\", \"企业部门\", \"企业体系\", \"门店名称\", \"门店等级\", \"毛利(AVG60)\", \"Features\"]).show(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f0b6d8b3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 拆分数据集。\n",
    "# --------------------------------------------------\n",
    "(TrainingData, TestData) = AssembledSDF.randomSplit([0.8, 0.2], seed=42)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "9f9a4dfa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 创建随机森林回归模型。\n",
    "# --------------------------------------------------\n",
    "RF = RandomForestRegressor(featuresCol=\"Features\", labelCol=\"毛利(AVG60)\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2d03abdc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 拟合模型。\n",
    "# --------------------------------------------------\n",
    "Model = RF.fit(TrainingData)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "0f87245b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+----------+----+--------+--------+--------------------+--------+-----------+-----------------+\n",
      "|      日期|星期|企业部门|企业体系|            门店名称|门店等级|毛利(AVG60)|       prediction|\n",
      "+----------+----+--------+--------+--------------------+--------+-----------+-----------------+\n",
      "|2020-01-03|周五|    二部|    华为|四川温江文庙街华为店|       C|    2985.62|6072.310709818154|\n",
      "|2020-01-07|周二|    二部|    华为|四川温江文庙街华为店|       C|  2288.9414|6072.310709818154|\n",
      "|2020-01-09|周四|    二部|    华为|四川温江文庙街华为店|       C|  2065.4311|6072.310709818154|\n",
      "|2020-01-14|周二|    二部|    华为|四川温江文庙街华为店|       C|  2462.1885|6072.310709818154|\n",
      "|2020-01-20|周一|    二部|    华为|四川温江文庙街华为店|       C|     3119.3|6072.310709818154|\n",
      "|2020-01-24|周五|    二部|    华为|四川温江文庙街华为店|       C|  3609.1023|6072.310709818154|\n",
      "|2020-01-30|周四|    二部|    华为|四川温江文庙街华为店|       C|  3325.9275|6072.310709818154|\n",
      "|2020-02-05|周三|    二部|    华为|四川温江文庙街华为店|       C|  2986.6146|6072.310709818154|\n",
      "|2020-02-26|周三|    二部|    华为|四川温江文庙街华为店|       C|  2710.1795|6072.310709818154|\n",
      "|2020-02-27|周四|    二部|    华为|四川温江文庙街华为店|       C|  2668.8391|6072.310709818154|\n",
      "|2020-02-28|周五|    二部|    华为|四川温江文庙街华为店|       C|  2623.4343|6072.310709818154|\n",
      "|2020-03-01|周日|    二部|    华为|四川温江文庙街华为店|       C|  2600.9169|5795.743280978786|\n",
      "|2020-03-03|周二|    二部|    华为|四川温江文庙街华为店|       C|  2587.1815|5795.743280978786|\n",
      "|2020-03-07|周六|    二部|    华为|四川温江文庙街华为店|       C|  2470.1408|5795.743280978786|\n",
      "|2020-03-14|周六|    二部|    华为|四川温江文庙街华为店|       C|  2412.8907|5795.743280978786|\n",
      "|2020-03-21|周六|    二部|    华为|四川温江文庙街华为店|       C|  2569.4358|5795.743280978786|\n",
      "|2020-03-29|周日|    二部|    华为|四川温江文庙街华为店|       C|  2368.2622|5795.743280978786|\n",
      "|2020-04-21|周二|    二部|    华为|四川温江文庙街华为店|       C|   2328.921|5728.253358296621|\n",
      "|2020-05-03|周日|    二部|    华为|四川温江文庙街华为店|       C|  2672.6349|5728.253358296621|\n",
      "|2020-05-07|周四|    二部|    华为|四川温江文庙街华为店|       C|  2790.0547|5728.253358296621|\n",
      "+----------+----+--------+--------+--------------------+--------+-----------+-----------------+\n",
      "only showing top 20 rows\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 进行预测。\n",
    "# --------------------------------------------------\n",
    "Predictions = Model.transform(TestData)\n",
    "Predictions.select([\"日期\", \"星期\", \"企业部门\", \"企业体系\", \"门店名称\", \"门店等级\", \"毛利(AVG60)\", \"prediction\"]).show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "e7c5acfa",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "均方误差(MSE): 8757777.227341\n"
     ]
    }
   ],
   "source": [
    "# 评估模型\n",
    "# --------------------------------------------------\n",
    "MyEvaluator = RegressionEvaluator(labelCol=\"毛利(AVG60)\", predictionCol=\"prediction\", metricName=\"mse\")\n",
    "mse = MyEvaluator.evaluate(Predictions)\n",
    "# ..................................................\n",
    "print(\"均方误差(MSE): %f\" % mse)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "db9a0a97",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "DataFrame[日期: date, 年: bigint, 月: bigint, 企业部门: string, 企业体系: string, 门店名称: string, 门店等级: string, 区域类别: string, 区域商圈: string, 毛利: double, 毛利(AVG60): double, 星期(Idx): bigint, 星期: string, 门店等级(Idx): bigint]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 释放缓存的数据(Release Cached RDD/DF)。\n",
    "# --------------------------------------------------\n",
    "CalcSDF.unpersist() # -> 释放缓存的 RDD/DF。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "481cb6aa",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 均方误差（Mean Squared Error，MSE）：表示预测值与真实值之间的平均差的平方。MSE越小，表示模型预测越准确。\n",
    "# 平均绝对误差（Mean Absolute Error，MAE）：表示预测值与真实值之间的平均绝对差。MAE越小，表示模型预测越准确。\n",
    "# R平方（R-squared，R2）：表示模型解释方差的比例，取值范围在0和1之间，越接近1表示模型的解释能力越强。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "786ceb0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# EOF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1714ae3d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
